package persistence.database;

import java.sql.PreparedStatement;
import java.sql.Date;
import java.sql.SQLException;

import core.exception.SaveException;

import persistence.DataPersistor;
import persistence.ItemDAO;
import persistence.ItemDTO;

/**
 * Item DAO for Database persistence
 * @author Justin
 *
 */
public class DBItemDAO implements ItemDAO {

	private DataPersistor _DP;
	private DBTransactionManager _TM;
	
	/**
	 * Constructs a DBItemDAO object
	 */
	public DBItemDAO()
	{
		_DP = DataPersistor.instance();
		_TM = (DBTransactionManager)_DP.getTransactionManager();
	}
	
	/**
	 * @inheritDoc
	 */
	@Override
	public void addItem(ItemDTO dto) throws SaveException {
		PreparedStatement stmt = null;
		
		long barcode = dto.getItemBarcode();
		//this Date object is from java.sql, NOT java.util
		Date entryDate = new Date(dto.getEntryDate().getTime().getTime());
		Date exitDate = null;
		if(dto.getExitDate() != null){
			exitDate = new Date(dto.getExitDate().getTime().getTime());
		}
		String productID = dto.getProductBarcode();
		int containerID = dto.getContainerID();
		//barcode, entry date, exit date, product, container
		//String addSQL = "INSERT INTO Items VALUES(" + barcode + ", " + entryDate + ", " + 
		//exitDate + ", \"" + productID + "\", " + containerID +");";
		String addSQL = "INSERT INTO Items (Barcode, EntryDate, ExitDate,"
		+ " Product, Container) VALUES(?, ?, ?, ?, ?)";
		
		//get a DB connection		
		//create a prepared statement
		try{
			stmt = _TM.getConnection().prepareStatement(addSQL);
			stmt.setLong(1, barcode);
			stmt.setDate(2, entryDate);
			stmt.setDate(3, exitDate);
			stmt.setString(4, productID);
			stmt.setInt(5, containerID);
			//execute the query
			if(stmt.executeUpdate() != 1){
				throw new SaveException("SQLite: Could not add Item");
			}
		}
		catch (SQLException e){
			throw new SaveException(e.getMessage());
		}
		finally {
			try {
				if(stmt != null) stmt.close();
			}
			catch (SQLException e) {
				//ignore
			}
		}
	}

	/**
	 * @inheritDoc
	 */
	@Override
	public void updateItem(ItemDTO dto) throws SaveException {
		PreparedStatement stmt = null;
		
		long barcode = dto.getItemBarcode();
		Date entryDate = new Date(dto.getEntryDate().getTime().getTime());
		Date exitDate = null;
		if(dto.getExitDate() != null){
			exitDate = new Date(dto.getExitDate().getTime().getTime());
		}
		String productID = dto.getProductBarcode();
		int containerID = dto.getContainerID();
		
		//String updateSQL = "UPDATE Items SET " + "EntryDate=" + entryDate + ", " + 
		//"ExitDate=" + exitDate + ", "	+ "ProductID=\"" + productID + "\", "+ "ContainerID=" + 
		//containerID + " "+ " WHERE Barcode=" + barcode + ";";
		String updateSQL = "UPDATE Items SET EntryDate = ?, ExitDate = ?, Product = ?," 
		+ " Container = ? WHERE Barcode = ?;";
		//get a DB connection		
		//create a prepared statement
		try{
			stmt = _TM.getConnection().prepareStatement(updateSQL);
			stmt.setDate(1, entryDate);
			stmt.setDate(2, exitDate);
			stmt.setString(3, productID);
			stmt.setInt(4, containerID);
			stmt.setLong(5, barcode);
			//execute the statement
			if(stmt.executeUpdate() != 1){
				throw new SaveException("SQLite: Could not update Item");
			}
		}
		catch(SQLException e){
			throw new SaveException(e.getMessage());
		}
		finally {
			try {
				if(stmt != null) stmt.close();
			}
			catch (SQLException e) {
				//ignore
			}
		}
	}

	/**
	 * @inheritDoc
	 */
	@Override
	public void removeItem(ItemDTO dto) throws SaveException {
		PreparedStatement stmt = null;
		
		long barcode = dto.getItemBarcode();
		//String removeSQL = "DELETE FROM Items WHERE Barcode=" + barcode + ";";
		String removeSQL = "DELETE FROM Items WHERE Barcode=?;";
		
		//get a DB connection
		//create a prepared statement
		try {
			stmt = _TM.getConnection().prepareStatement(removeSQL);
			stmt.setLong(1, barcode);
			
			//execute the statement
			if(stmt.executeUpdate() != 1){
				throw new SaveException("SQLite: Could not remove item");
			}
		}
		catch(SQLException e){
			throw new SaveException(e.getMessage());
		}
		finally {
			try {
				if(stmt != null) stmt.close();
			}
			catch (SQLException e) {
				//ignore
			}
		}
	}

}
